package com.katian;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.util.*;

/**
 * User: csavard
 * Date: 13-05-14
 */
public class MergerUtil {

    public static final int CAISSE_COLUMN = 9;
    public static final String DOLLARS_FORMAT = "\"$\"#,##0.00_);[Red]\\(\"$\"#,##0.00\\)";

    public static Map<String, CommissionItem> getCommissionItem(InputStream in) throws IOException {
        Map<String, CommissionItem> itemsMap = new LinkedHashMap<>();

        Workbook wb = new XSSFWorkbook(in);
        Sheet sheet = wb.getSheetAt(0);
        int rowindex = 1;
        while (rowindex <= sheet.getLastRowNum()) {
            Row row = sheet.getRow(rowindex);
            if (row != null) {

                Cell nameCell = row.getCell(2);
                if (nameCell != null) {
                    CommissionItem item = new CommissionItem();

                    String name = nameCell.getStringCellValue();

                    String externalDocumentNo = row.getCell(4).getStringCellValue();
                    String description = row.getCell(7).getStringCellValue();

                    item.setName(name);
                    if (externalDocumentNo.startsWith("B")) {
                        String idString = externalDocumentNo.substring(5, externalDocumentNo.length());
                        if (idString.endsWith(".")) {
                            idString = idString.substring(0, idString.length() - 1);
                        }
                        item.setId(idString);
                    } else if (externalDocumentNo.startsWith("0")) {
                        String idString = description;
                        if (idString.startsWith("0")) {
                            idString = idString.substring(4, idString.length());
                        }

                        idString = idString.substring(0, idString.length() - 2);
                        item.setId(idString);
                    } else {
                        item.setName(description);
                    }

                    itemsMap.put(item.getId(), item);
                }
            }
            rowindex++;
        }
        return itemsMap;
    }

    public static Map<Long, Produit> getProduct(InputStream in) throws IOException {
        Map<Long, Produit> productMap = new LinkedHashMap<>();

        Workbook wb = new XSSFWorkbook(in);
        Sheet sheet = wb.getSheetAt(0);
        int rowindex = 1;
        while (rowindex <= sheet.getLastRowNum()) {
            Row row = sheet.getRow(rowindex);
            if (row != null) {
                Produit produit = new Produit();
                double code = row.getCell(1).getNumericCellValue();
                String name = row.getCell(2).getStringCellValue();
                double uniteParCaisse = row.getCell(4).getNumericCellValue();

                produit.setCode((long) code);

                produit.setNom(name);
                produit.setUniteParCaisse((int) uniteParCaisse);

                productMap.put(produit.getCode(), produit);
            }
            rowindex++;
        }
        return productMap;
    }

    public static Map<Long, Commande> getVentes(InputStream in, Map<Long, Produit> produits) throws IOException {
        Map<Long, Commande> commandeMap = new LinkedHashMap<>();

        Workbook wb = new XSSFWorkbook(in);
        Sheet sheet = wb.getSheetAt(0);
        int rowindex = 1;
        while (rowindex <= sheet.getLastRowNum()) {
            Row row = sheet.getRow(rowindex);
            if (row != null) {
                String noString = row.getCell(31).getStringCellValue();
                long no = Long.valueOf(noString.substring(1, noString.length() - 1));
                Commande commande = commandeMap.get(no);
                if (commande == null) {
                    commande = new Commande();
                    commande.setNo(no);
                    commandeMap.put(commande.getNo(), commande);
                }

                String productCodeString = row.getCell(5).getStringCellValue();
                long productCode = Long.valueOf(productCodeString.substring(1, productCodeString.length() - 1));

                Produit produit = produits.get(productCode);
                if (produit == null) {
                    produit = new Produit();
                    produit.setCode(productCode);
                    produit.setNom("Produit inconnue");
                    produit.setUnknow(true);
                }
                int pack = (int) row.getCell(7).getNumericCellValue();

                Collection<Commande.ProduitItem> produitItems = commande.getProduitItems();
                if (produitItems == null) {
                    produitItems = new Vector<>();
                    commande.setProduitItems(produitItems);
                }

                Commande.ProduitItem item = new Commande.ProduitItem();
                item.setProduit(produit);
                item.setNb(pack);
                produitItems.add(item);
            }
            rowindex++;
        }
        return commandeMap;
    }

    public static void generateCommision(XSSFWorkbook wb, Map<String, CommissionItem> commissionItemMap) throws IOException {
        DataFormat dataFormat = wb.createDataFormat();

        CellStyle dateCellStyle = null;
        CellStyle dollarsCellStyle = null;
        CellStyle percentageCellStyle = null;


        Sheet sheet = wb.getSheetAt(2);
        Row titleRow = sheet.getRow(6);
        Cell refTitleCell = titleRow.getCell(13);
        refTitleCell.setCellValue("REFERENCE");
        refTitleCell.setCellStyle(titleRow.getCell(12).getCellStyle());

        int rowindex = 8;
        while (rowindex <= sheet.getLastRowNum()) {
            Row row = sheet.getRow(rowindex);
            if (row != null) {
                //delete la premiere ligne en shiftant les autres lignes par en haut
                sheet.shiftRows(rowindex, rowindex, -1);

                Cell invoiceDateCell = row.getCell(0);
                Cell idCell = row.getCell(2);
                Cell orderNoCell = row.getCell(3);
                Cell netTotalCell = row.getCell(6);
                Cell commPercentCell = row.getCell(7);
                Cell commDollarsCell = row.getCell(8);
                Cell tpsCell = row.getCell(9);
                Cell tvqCell = row.getCell(10);
                Cell commDueCell = row.getCell(11);
                Cell confirmedCell = row.getCell(12);
                Cell nameCell = row.getCell(13);

                //Ajustement de la date
                if (dateCellStyle == null) {
                    dateCellStyle = wb.createCellStyle();
                    dateCellStyle.cloneStyleFrom(invoiceDateCell.getCellStyle());
                    dateCellStyle.setDataFormat(dataFormat.getFormat("yyyy-mm-dd"));
                }
                invoiceDateCell.setCellStyle(dateCellStyle);
                confirmedCell.setCellStyle(dateCellStyle);

                String idString = idCell.getStringCellValue();
                long id = 0;
                if (idString.startsWith("0000")) {
                    idString = idString.substring(4, idString.length());
                    id = Long.valueOf(idString);
                    idCell.setCellValue(id);
                }

                String orderNoCellString = orderNoCell.getStringCellValue();
                orderNoCell.setCellValue(Double.valueOf(orderNoCellString));

                if (dollarsCellStyle == null) {
                    dollarsCellStyle = wb.createCellStyle();
                    dollarsCellStyle.cloneStyleFrom(netTotalCell.getCellStyle());
                    short format = dataFormat.getFormat(DOLLARS_FORMAT);
                    dollarsCellStyle.setDataFormat(format);
                }
                netTotalCell.setCellStyle(dollarsCellStyle);
                commDollarsCell.setCellStyle(dollarsCellStyle);
                tpsCell.setCellStyle(dollarsCellStyle);
                tvqCell.setCellStyle(dollarsCellStyle);
                commDueCell.setCellStyle(dollarsCellStyle);

                if (percentageCellStyle == null) {
                    percentageCellStyle = wb.createCellStyle();
                    percentageCellStyle.cloneStyleFrom(commPercentCell.getCellStyle());
                    short format = dataFormat.getFormat("0.00%");
                    percentageCellStyle.setDataFormat(format);
                }
                commPercentCell.setCellStyle(percentageCellStyle);
                CommissionItem commissionItem = commissionItemMap.get(idString);
                if (commissionItem != null) {
                    nameCell.setCellValue(commissionItem.getName());
                }
            }
            rowindex++;
        }
    }

    public static void generateBuyBack(XSSFWorkbook wb, Map<String, CommissionItem> commissionItemMap, Map<Long, Commande> ventes) throws IOException {
        DataFormat dataFormat = wb.createDataFormat();

        CellStyle dateCellStyle = null;
        CellStyle dollarsCellStyle = null;
        CellStyle percentageCellStyle = null;
        CellStyle numberCellStyle = null;


        Sheet sheet = wb.getSheetAt(3);
        Row titleRow = sheet.getRow(6);
        insertColumn((XSSFRow) titleRow, CAISSE_COLUMN);
        titleRow.getCell(CAISSE_COLUMN).setCellValue("Caisse");
        int rowindex = 8;
        while (rowindex <= sheet.getLastRowNum()) {
            Row row = sheet.getRow(rowindex);
            if (row != null) {
                insertColumn((XSSFRow) row, CAISSE_COLUMN);
                //delete la premiere ligne en shiftant les autres lignes par en haut
                sheet.shiftRows(rowindex, rowindex, -1);

                Cell dateCell = row.getCell(0);
                Cell idCell = row.getCell(3);
                Cell noCommandCell = row.getCell(4);
                Cell classeCell = row.getCell(6);
                Cell totalCell = row.getCell(7);
                Cell commPercentCell = row.getCell(8);
                Cell caisseCell = row.getCell(CAISSE_COLUMN);
                Cell commDollarCell = row.getCell(10);
                Cell tpsCell = row.getCell(11);
                Cell tvqCell = row.getCell(12);
                Cell commDueCell = row.getCell(13);
                Cell confirmeCell = row.getCell(14);
                Cell nameCell = row.getCell(15);

                //Ajustement de la date
                if (dateCellStyle == null) {
                    dateCellStyle = wb.createCellStyle();
                    dateCellStyle.cloneStyleFrom(dateCell.getCellStyle());
                    dateCellStyle.setDataFormat(dataFormat.getFormat("yyyy-mm-dd"));
                }
                dateCell.setCellStyle(dateCellStyle);
                confirmeCell.setCellStyle(dateCellStyle);

                String idString = idCell.getStringCellValue();
                long id = 0;
                if (idString.startsWith("0000")) {
                    idString = idString.substring(4, idString.length());
                    id = Long.valueOf(idString);
                    idCell.setCellValue(id);
                }

                String orderNoCellString = noCommandCell.getStringCellValue();
                Long noCommande = Long.parseLong(orderNoCellString);
                noCommandCell.setCellValue(noCommande);


                if (dollarsCellStyle == null) {
                    dollarsCellStyle = wb.createCellStyle();
                    dollarsCellStyle.cloneStyleFrom(totalCell.getCellStyle());
                    short format = dataFormat.getFormat(DOLLARS_FORMAT);
                    dollarsCellStyle.setDataFormat(format);
                }

                if (percentageCellStyle == null) {
                    percentageCellStyle = wb.createCellStyle();
                    percentageCellStyle.cloneStyleFrom(commPercentCell.getCellStyle());
                    short format = dataFormat.getFormat("0.00%");
                    percentageCellStyle.setDataFormat(format);
                }

                totalCell.setCellStyle(dollarsCellStyle);
                commDollarCell.setCellStyle(dollarsCellStyle);
                tpsCell.setCellStyle(dollarsCellStyle);
                tvqCell.setCellStyle(dollarsCellStyle);
                commDueCell.setCellStyle(dollarsCellStyle);

                String classValue = classeCell.getStringCellValue();
                if (classValue.equalsIgnoreCase("A") || classValue.equalsIgnoreCase("B")) {
                    commPercentCell.setCellStyle(dollarsCellStyle);

                    Commande commande = ventes.get(noCommande);
                    caisseCell.setCellValue(commande.getNbCaisse());

                    if (numberCellStyle == null) {
                        numberCellStyle = wb.createCellStyle();
                        numberCellStyle.cloneStyleFrom(caisseCell.getCellStyle());
                        short format = dataFormat.getFormat("0.00");
                        numberCellStyle.setDataFormat(format);
                    }
                    caisseCell.setCellStyle(numberCellStyle);
                } else {
                    commPercentCell.setCellStyle(percentageCellStyle);
                }

                CommissionItem commissionItem = commissionItemMap.get(idString);
                if (commissionItem != null) {
                    nameCell.setCellValue(commissionItem.getName());
                }
            }
            rowindex++;
        }
    }

    public static void generateAchat(XSSFWorkbook wb) {
        CellStyle dateCellStyle = null;
        CellStyle dollarsCellStyle = null;


        Sheet sheet = wb.getSheetAt(4);

        sheet.getRow(6).getCell(10).setCellValue("REFERENCE");

        //delete les ligne qui ne sont pas des données
        int rowindex = 7;
        while (rowindex <= sheet.getLastRowNum()) {
            Row row = sheet.getRow(rowindex);
            if (row != null) {
                XSSFCell cell = (XSSFCell) row.getCell(0);
                if (cell.getCellType() != XSSFCell.CELL_TYPE_NUMERIC) {
                    sheet.shiftRows(rowindex + 1, sheet.getLastRowNum(), -1);
                }
            }
            rowindex++;
        }

        //formattage  des ligne
        rowindex = 7;
        while (rowindex <= sheet.getLastRowNum()) {
            Row row = sheet.getRow(rowindex);
            if (row != null) {
                Cell invoiceDateCell = row.getCell(0);
                Cell invoiceNoCell = row.getCell(2);
                Cell orderNoCell = row.getCell(3);
                Cell netTotalCell = row.getCell(5);
                Cell tpsCell = row.getCell(6);
                Cell tvqCell = row.getCell(7);
                Cell totalCell = row.getCell(8);
                Cell confirmationCell = row.getCell(9);

                String invoiceNoStr = invoiceNoCell.getStringCellValue();
                if (invoiceNoStr.startsWith("0")) {
                    invoiceNoCell.setCellValue(Long.valueOf(invoiceNoStr));
                }

                String orderNoStr = orderNoCell.getStringCellValue();
                if (orderNoStr.startsWith("0")) {
                    orderNoCell.setCellValue(Long.valueOf(orderNoStr));
                }

                String tps = tpsCell.getStringCellValue();
                if (!tps.isEmpty()) {
                    tpsCell.setCellValue(Double.valueOf(tps));
                }

                String tvq = tvqCell.getStringCellValue();
                if (!tvq.isEmpty()) {
                    tvqCell.setCellValue(Double.valueOf(tvq));
                }


                //Set le format des cellules
                if (dateCellStyle == null) {
                    dateCellStyle = getDateCellStyle(wb, invoiceDateCell);
                }
                if (dollarsCellStyle == null) {
                    dollarsCellStyle = getDollarsCellStyle(wb, netTotalCell);
                }

                invoiceDateCell.setCellStyle(dateCellStyle);
                netTotalCell.setCellStyle(dollarsCellStyle);
                tpsCell.setCellStyle(dollarsCellStyle);
                tvqCell.setCellStyle(dollarsCellStyle);
                totalCell.setCellStyle(dollarsCellStyle);
                confirmationCell.setCellStyle(dateCellStyle);
            }
            rowindex++;
        }
    }

    public static void generateUnconfirmedBS(XSSFWorkbook wb) {
        CellStyle dateCellStyle = null;
        CellStyle dollarsCellStyle = null;


        Sheet sheet = wb.getSheetAt(5);

        //Insert la column No Client
        XSSFRow titleRow = (XSSFRow) sheet.getRow(6);
        insertColumn(titleRow, 4);
        titleRow.getCell(4).setCellValue(titleRow.getCell(7).getStringCellValue());
        titleRow.getCell(7).setCellValue("");
        titleRow.getCell(8).setCellValue("");
        titleRow.getCell(9).setCellValue("");

        //formattage  des ligne
        int rowindex = 7;
        while (rowindex <= sheet.getLastRowNum()) {
            Row row = sheet.getRow(rowindex);
            if (row != null) {
                //Insert la column No Client
                insertColumn((XSSFRow) row, 4);

                Cell dateFactureCell = row.getCell(0);
                Cell noFactureCell = row.getCell(2);
                Cell orderNoCell = row.getCell(3);
                Cell newNoClientCell = row.getCell(4);
                Cell totalCell = row.getCell(6);
                Cell oldNoClientCell = row.getCell(7);
                Cell distributeurCell = row.getCell(8);
                Cell sousDistributeurCell = row.getCell(9);

                String invoiceNoStr = noFactureCell.getStringCellValue();
                if (invoiceNoStr.startsWith("0")) {
                    noFactureCell.setCellValue(Long.valueOf(invoiceNoStr));
                }

                String orderNoStr = orderNoCell.getStringCellValue();
                if (orderNoStr.startsWith("0")) {
                    orderNoCell.setCellValue(Long.valueOf(orderNoStr));
                }

                newNoClientCell.setCellValue(oldNoClientCell.getStringCellValue());

                //Set le format des cellules
                if (dateCellStyle == null) {
                    dateCellStyle = getDateCellStyle(wb, dateFactureCell);
                }
                if (dollarsCellStyle == null) {
                    dollarsCellStyle = getDollarsCellStyle(wb, totalCell);
                }

                dateFactureCell.setCellStyle(dateCellStyle);
                totalCell.setCellStyle(dollarsCellStyle);

                //Efface les valeur inutile
                oldNoClientCell.setCellValue("");
                distributeurCell.setCellValue("");
                sousDistributeurCell.setCellValue("");
            }
            rowindex++;
        }
    }

    public static void generateUnconfirmedBB(XSSFWorkbook wb) {
        CellStyle dateCellStyle = null;
        CellStyle dollarsCellStyle = null;


        Sheet sheet = wb.getSheetAt(6);
        XSSFRow titleRow = (XSSFRow) sheet.getRow(6);
        titleRow.getCell(8).setCellValue("");
        titleRow.getCell(9).setCellValue("");
        titleRow.getCell(10).setCellValue("");
        titleRow.getCell(11).setCellValue("");

        //formattage  des ligne
        int rowindex = 7;
        while (rowindex <= sheet.getLastRowNum()) {
            Row row = sheet.getRow(rowindex);
            if (row != null) {
                Cell dateCell = row.getCell(0);
                Cell noLivraisonCell = row.getCell(4);
                Cell noCommandeCell = row.getCell(5);
                Cell totalCell = row.getCell(7);
                Cell entrepotCell = row.getCell(8);
                Cell confirmationCell = row.getCell(9);
                Cell sousDistributeurCell = row.getCell(10);
                Cell validCell = row.getCell(11);

                String noLivraison = noLivraisonCell.getStringCellValue();
                if (noLivraison.startsWith("0")) {
                    noLivraisonCell.setCellValue(Long.valueOf(noLivraison));
                }

                String noCommandeStr = noCommandeCell.getStringCellValue();
                if (noCommandeStr.startsWith("0")) {
                    noCommandeCell.setCellValue(Long.valueOf(noCommandeStr));
                }

                //Set le format des cellules
                if (dateCellStyle == null) {
                    dateCellStyle = getDateCellStyle(wb, dateCell);
                }
                if (dollarsCellStyle == null) {
                    dollarsCellStyle = getDollarsCellStyle(wb, totalCell);
                }

                dateCell.setCellStyle(dateCellStyle);
                totalCell.setCellStyle(dollarsCellStyle);

                //Efface les valeur inutile
                entrepotCell.setCellValue("");
                if (confirmationCell != null) {
                    confirmationCell.setCellValue("");
                }
                if (sousDistributeurCell != null) {
                    sousDistributeurCell.setCellValue("");
                }
                if (validCell != null) {
                    validCell.setCellValue("");
                }
            }
            rowindex++;
        }
    }

    private static void insertColumn(XSSFRow row, int index) {
        short lastColumn = row.getLastCellNum();
        for (int i = lastColumn; i >= index; i--) {
            XSSFCell cellOld = row.getCell(i);
            XSSFCell cellNew = row.getCell(i + 1);
            if (cellOld != null && cellNew != null) {
                copyCell(cellOld, cellNew);
            }

        }
        row.getCell(index).setCellValue("");
    }

    private static void copyCell(XSSFCell cellOld, XSSFCell cellNew) {
        cellNew.setCellStyle(cellOld.getCellStyle());
        switch (cellOld.getCellType()) {
            case XSSFCell.CELL_TYPE_STRING:
                cellNew.setCellValue(cellOld.getStringCellValue());
                break;
            case XSSFCell.CELL_TYPE_NUMERIC:
                cellNew.setCellValue(cellOld.getNumericCellValue());
                break;
            case XSSFCell.CELL_TYPE_BLANK:
                cellNew.setCellValue(XSSFCell.CELL_TYPE_BLANK);
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN:
                cellNew.setCellValue(cellOld.getBooleanCellValue());
                break;
            case XSSFCell.CELL_TYPE_FORMULA:
                cellNew.setCellFormula(cellOld.getCellFormula());
                break;
        }
    }

    public static CellStyle getDateCellStyle(XSSFWorkbook wb, Cell template) {
        DataFormat dataFormat = wb.createDataFormat();

        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.cloneStyleFrom(template.getCellStyle());
        short format = dataFormat.getFormat("yyyy-mm-dd");
        cellStyle.setDataFormat(format);

        return cellStyle;
    }

    public static CellStyle getDollarsCellStyle(XSSFWorkbook wb, Cell template) {
        DataFormat dataFormat = wb.createDataFormat();

        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.cloneStyleFrom(template.getCellStyle());
        short format = dataFormat.getFormat(DOLLARS_FORMAT);
        cellStyle.setDataFormat(format);

        return cellStyle;
    }
}
